 IF EXISTS (
	SELECT *
	FROM sysobjects
	WHERE id = object_id(N'dbo.spFdsNHumanReviewInject_Boards')
	AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	
	BEGIN
		
		PRINT 'Dropping spFdsNHumanReviewInject_Boards'
        DROP PROCEDURE dbo.spFdsNHumanReviewInject_Boards
        
	END

GO
	
/*******************************************************************************
* Name:					spFdsNHumanReviewInject_Boards
* Purpose:				Injects new RevReviewBoard records from the staging
*						table and updates the staging table with the newly
*						inserted ids.
*

* ERRORS USED			Description
* -------------			--------------------------------------------------------
* 
*
* RETURN VALUE
* Value    				Description					
* --------------		--------------------------------------------------------
* @_exitStatus		An exit status of either true or false
********************************************************************************/
CREATE PROCEDURE spFdsNHumanReviewInject_Boards
AS

BEGIN

	SET NOCOUNT ON

	DECLARE @_stagedBoardReviewId		AS INTEGER
	DECLARE @_revReviewId				AS INTEGER
	
	
	DECLARE @_TRUE AS INTEGER
	SET @_TRUE = 1
	
	DECLARE @_SUCCESS AS BIT
	DECLARE @_FAILURE AS BIT
	SET @_SUCCESS = 1
	SET @_FAILURE = 0
	
	
	
	-- select the set of new board reviews and open up a cursor on that data
	DECLARE cursorNewBoardReviewIds CURSOR LOCAL FORWARD_ONLY FOR
		SELECT		DISTINCT(board.Id), ReviewStaging.RevReviewId
		FROM		FdsProtocolStagingProtocolReview ReviewStaging
		INNER JOIN	FdsProtocolStagingBoardReview board
			ON		board.RexReviewId = ReviewStaging.RexReviewId
			AND		board.RexProtocolId = ReviewStaging.RexProtocolId
		INNER JOIN	FdsProtocolStagingBoardReviewDetail detail
			ON		detail.RexProtocolId = board.RexProtocolId
			AND		detail.RexReviewId = board.RexReviewId
			AND		detail.RexMailGroupId = board.RexMailGroupId
		WHERE		board.RevBoardReviewId IS NULL
	
	BEGIN TRANSACTION spFdsNHumanReviewInject_Boards
	
	-- iterate over each new board review inserting the data to the board
	-- review table and updating the staging table with the new board review ID
	OPEN cursorNewBoardReviewIds
	FETCH NEXT FROM cursorNewBoardReviewIds INTO @_stagedBoardReviewId, @_revReviewId
	WHILE @@FETCH_STATUS = 0
  	BEGIN
		    
		-- insert the new board review data
		INSERT INTO RevBoardReview (
				RevReviewId
				,RevBoardId
				,RevTypeId )
		SELECT	@_revReviewId
				,BoardReviewStaging.RevBoardId
				,BoardReviewStaging.RevTypeId
		FROM	FdsProtocolStagingBoardReview BoardReviewStaging
		WHERE	BoardReviewStaging.Id = @_stagedBoardReviewId
			
		IF @@ERROR > 0 GOTO ERROR_HANDLER
		
		-- update staging table with new review ID
		UPDATE		FdsProtocolStagingBoardReview
  		SET			RevBoardReviewId = SCOPE_IDENTITY()
  		WHERE		Id = @_stagedBoardReviewId
  		
  		-- insert the REX board review data
  		INSERT INTO RevRexBoardReview (
				RevBoardReviewId
				,RexProtocolId
				,RexReviewId
				,RexMailGroupId )
		SELECT	BoardReviewStaging.RevBoardReviewId
				,BoardReviewStaging.RexProtocolId
				,BoardReviewStaging.RexReviewId
				,BoardReviewStaging.RexMailGroupId
		FROM	FdsProtocolStagingBoardReview BoardReviewStaging
		WHERE	BoardReviewStaging.Id = @_stagedBoardReviewId
			
		IF @@ERROR > 0 GOTO ERROR_HANDLER

      	-- get next board review ID from list
		FETCH NEXT FROM cursorNewBoardReviewIds INTO @_stagedBoardReviewId, @_revReviewId
	
   	END
   	
   	COMMIT TRANSACTION spFdsNHumanReviewInject_Boards
   	
	-- close and deallocate cursor
	CLOSE cursorNewBoardReviewIds
  	DEALLOCATE cursorNewBoardReviewIds
  	
  	SET NOCOUNT OFF
  	
	RETURN @_SUCCESS

ERROR_HANDLER:
	BEGIN
		ROLLBACK TRANSACTION spFdsNHumanReviewInject_Boards
		RETURN @_FAILURE
	END
END